﻿/******************************************************   
创建者: ljj
创建时间: 2017-12-14
修改:
修改时间: 
功能: 联销报表
 ******************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Activity.Data;
using Activity.Helper;
using Activity.Model;
using Activity.Redis;
using com.km.Cache;
using Nancy.Extensions;
using Newtonsoft.Json;

namespace Activity.API
{
    public class VendCustSalesReportHandler
    {
        public VendCustSalesReportModel.ConditionModel GetCondition(int UserID)
        {
            try
            {
                //var userID = com.km.library.UserInfo.UserID;
                var companyID = "KM";

                var rpt = new ActivityEntities();
                var hkerp = new HKERPEntities();
                var vendCustList = rpt.V_UserVendDataRights.Where(d => d.UserID == UserID).Select(
                    d => new VendCustSalesReportModel.VendCustModel
                    {
                        VendCustID = d.VendCustID,
                        VendCustName = d.VendCustName,
                        UserID = d.UserID
                    }).DistinctBy(d => new {d.VendCustName, d.VendCustID}).OrderBy(d => d.VendCustID).ToList();

                var operationAreaList = hkerp.Bas_Area.Where(d => d.Level == 1).Select(
                    d => new VendCustSalesReportModel.AreaModel
                    {
                        AreaID = d.AreaID,
                        AreaName = d.AreaName
                    }).DistinctBy(d => d.AreaID).OrderBy(d => d.AreaID).ToList();
                //var json = JsonHelper.GetFileJson("~/VendCust/ShopNature.json");
                //var shopNatureList = json.FromJson<IList<VendCustSalesReportModel.ShopNatureModel>>();
                var matSeriesList = hkerp.SD_Mat_Series.Select(d => new VendCustSalesReportModel.MatSeriesModel
                {
                    SeriesID = d.SeriesID,
                    SeriesName = d.SeriesName
                }).OrderBy(d => d.SeriesID).ToList();
                var matModelList = hkerp.SD_Mat_Model.Select(d => new VendCustSalesReportModel.MatModelModel
                {
                    ModelID = d.ModelID,
                    ModelName = d.ModelName
                }).OrderBy(d => d.ModelID).ToList();
                var matSeasonList = hkerp.SD_Mat_Season.Select(d => new VendCustSalesReportModel.MatSeasonModel
                {
                    SeasonID = d.SeasonID,
                    SeasonName = d.SeasonName
                }).OrderBy(d => d.SeasonID).ToList();
                var periodYearList = hkerp.Bas_Accperiod.Select(d => new VendCustSalesReportModel.PeriodYearModel
                {
                    PeriodYear = d.PeriodYear
                }).OrderBy(d => d.PeriodYear).ToList();

                var list = new VendCustSalesReportModel.ConditionModel()
                {
                    VendCustList = vendCustList,
                    OperationAreaList = operationAreaList,
                    //ShopNatureList = shopNatureList,
                    MatSeriesList = matSeriesList,
                    MatModelList = matModelList,
                    MatSeasonList = matSeasonList,
                    PeriodYearList = periodYearList,
                };
                return list;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public VendCustSalesReportModel.SaveModel GetVendCustSalesReport(string VendCustID, string StartDateStr,
            string EndDateStr, string PeriodYear, string SeasonID, string OperationAreaID, string ShopNature,
            string SeriesID, string ModelID, string MaterialID)
        {
            try
            {
                var sql =
                    $@"exec [dbo].[KM_spSD_VendProSale] 'KM','{VendCustID}','{OperationAreaID}','{PeriodYear}','{SeasonID}','{SeriesID}','{ModelID}','{MaterialID}','{StartDateStr}','{EndDateStr}'";
                //var sql =
                //    "exec KM_spSD_VendProSale 'KM','KM00000038','',2017,'000004','000001','','','2017-12-01','2017-12-10'";
                var db = new HKERPEntities();

                var resultTotalList = new List<VendCustSalesReportModel.ResultTotalModel>();
                var resultTotalProvList = new List<VendCustSalesReportModel.ResultTotalProvModel>();
                var resultDetailList = new List<VendCustSalesReportModel.ResultDetailModel>();

                using (var dbCon = db.Database.Connection)
                {
                    using (var sqlCon = (SqlConnection) dbCon)
                    {
                        var sda = new SqlDataAdapter(sql, sqlCon);
                        sda.SelectCommand.CommandTimeout = 0;
                        var ds = new DataSet();
                        sda.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            resultTotalList = CommHelper.ModelConvertHelper<VendCustSalesReportModel.ResultTotalModel>
                                .ConvertToModel(ds.Tables[0]).ToList();
                        }
                        if (ds.Tables.Count > 1)
                        {
                            resultTotalProvList = CommHelper
                                .ModelConvertHelper<VendCustSalesReportModel.ResultTotalProvModel>
                                .ConvertToModel(ds.Tables[1]).ToList();
                        }
                        if (ds.Tables.Count > 2)
                        {
                            resultDetailList = CommHelper
                                .ModelConvertHelper<VendCustSalesReportModel.ResultDetailModel>
                                .ConvertToModel(ds.Tables[2]).ToList();
                        }
                    }
                }

                var key = VendCustID + "_" + StartDateStr + "_" + EndDateStr + "_" + PeriodYear + "_" + SeasonID + "_" +
                          OperationAreaID + "_" + ShopNature + "_" + SeriesID + "_" + ModelID + "_" + MaterialID;

                var resultTotalListJson = resultTotalList.ToJson();
                var resultTotalListByte = Encoding.UTF8.GetBytes(resultTotalListJson);
                RedisCache.Add(key + "_resultTotalList", resultTotalListByte, 20);

                var resultTotalProvListJson = resultTotalProvList.ToJson();
                var resultTotalProvListByte = Encoding.UTF8.GetBytes(resultTotalProvListJson);
                RedisCache.Add(key + "_resultTotalProvList", resultTotalProvListByte, 20);

                var resultDetailListJson = resultDetailList.ToJson();
                var resultDetailListByte = Encoding.UTF8.GetBytes(resultDetailListJson);
                RedisCache.Add(key + "_resultDetailList", resultDetailListByte, 20);

                var result = new VendCustSalesReportModel.SaveModel()
                {
                    resultTotalList = resultTotalList,
                    resultTotalProvList = resultTotalProvList,
                    resultDetailList = resultDetailList,
                };

                return result;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        public VendCustSalesReportModel.SaveModel GetVendCustSalesReportFromRedis(string VendCustID, DateTime StartDate,
            DateTime EndDate, string PeriodYear, string SeasonID, string OperationAreaID, string ShopNature,
            string SeriesID, string ModelID, string MaterialID)
        {
            try
            {
                var startDateStr = $"{StartDate:yyyy-MM-dd}";
                var endDateStr = $"{EndDate:yyyy-MM-dd}";
                var key = VendCustID + "_" + startDateStr + "_" + endDateStr + "_" + PeriodYear + "_" + SeasonID + "_" +
                          OperationAreaID + "_" + ShopNature + "_" + SeriesID + "_" + ModelID + "_" + MaterialID;

                var result = new VendCustSalesReportModel.SaveModel();
                if (RedisCache.Exists(key + "_resultTotalList") && RedisCache.Exists(key + "_resultTotalProvList") &&
                    RedisCache.Exists(key + "_resultDetailList"))
                {
                    byte[] resultTotalListByte = RedisCache.Get(key + "_resultTotalList");
                    var resultTotalListJson = Encoding.UTF8.GetString(resultTotalListByte);
                    var resultTotalList =
                        resultTotalListJson.FromJson<IList<VendCustSalesReportModel.ResultTotalModel>>();

                    byte[] resultTotalProvListByte = RedisCache.Get(key + "_resultTotalProvList");
                    var resultTotalProvListJson = Encoding.UTF8.GetString(resultTotalProvListByte);
                    var resultTotalProvList = resultTotalProvListJson
                        .FromJson<IList<VendCustSalesReportModel.ResultTotalProvModel>>();

                    byte[] resultDetailListByte = RedisCache.Get(key + "_resultDetailList");
                    var resultDetailListJson = Encoding.UTF8.GetString(resultDetailListByte);
                    var resultDetailList = resultDetailListJson
                        .FromJson<IList<VendCustSalesReportModel.ResultDetailModel>>();

                    result.resultTotalList = resultTotalList;
                    result.resultTotalProvList = resultTotalProvList;
                    result.resultDetailList = resultDetailList;
                }
                else
                {
                    result = GetVendCustSalesReport(VendCustID, startDateStr, endDateStr, PeriodYear, SeasonID,
                        OperationAreaID, ShopNature, SeriesID, ModelID, MaterialID);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        public VendCustSalesReportModel.ShowModel GetVendCustSalesReport(string VendCustID, DateTime StartDate,
            DateTime EndDate, string PeriodYear, string SeasonID, string OperationAreaID = "", string ShopNature = "",
            string SeriesID = "", string ModelID = "", string MaterialID = "", int Page = 1, int Size = 100)
        {
            try
            {
                var query = GetVendCustSalesReportFromRedis(VendCustID, StartDate, EndDate, PeriodYear, SeasonID,
                    OperationAreaID, ShopNature, SeriesID, ModelID, MaterialID);
                var result = TranForm(query, Page, Size);
                return result;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public VendCustSalesReportModel.ShowModel TranForm(VendCustSalesReportModel.SaveModel param, int Page, int Size)
        {
            try
            {
                var group = param.resultDetailList
                    .GroupBy(d => d.Province + "_" + d.StockName + "_" + d.StockType + "_" + d.ShopType + "_" +
                                  d.VendCustName + "_" + d.MaterialID + "_" + d.CostPrice + "_" + d.ColorName + "_" +
                                  d.SaleRate);
                var count = group.Count();
                var totalPage = Convert.ToInt32(Math.Ceiling(count * 1.0 / Size));
                var resultTotalFinalList = param.resultTotalList.Select(
                    d => new VendCustSalesReportModel.ResultTotalFinalModel
                    {
                        MaterialID = d.MaterialID,
                        FactoryNo = d.FactoryNo,
                        ColorName = d.ColorName,
                        VendCustName = d.VendCustName,
                        DataText = d.DataText,
                        SaleDate = d.SaleDate,
                        SaleRate = d.SaleRate,
                        SaleAmount = d.SaleAmount,
                        Discount = d.Discount,
                        SaleQty = d.SaleQty,
                        CostPrice = d.CostPrice,
                        StockQty = d.StockQty,
                        ShopQty = d.ShopQty,
                        RowNumber = d.RowNumber,
                    }).ToList();
                foreach (var item in resultTotalFinalList)
                {
                    item.resultTotalProvList = param.resultTotalProvList.Where(d => d.MaterialID == item.MaterialID)
                        .Select(d => d).GroupBy(d => d.MaterialID + "_" + d.Province).ToList();
                }
                var result = new VendCustSalesReportModel.ShowModel
                {
                    resultTotalList = resultTotalFinalList,
                    resultDetailList = group.Skip((Page - 1) * Size).Take(Size).ToList(),
                    Count = totalPage,
                };
                return result;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        public DataSet ExportVendCustSalesReport(string VendCustID, DateTime StartDate,
            DateTime EndDate, string PeriodYear, string SeasonID, string OperationAreaID = "", string ShopNature = "",
            string SeriesID = "", string ModelID = "", string MaterialID = "")
        {
            try
            {
                var query = GetVendCustSalesReportFromRedis(VendCustID, StartDate, EndDate, PeriodYear, SeasonID,
                    OperationAreaID, ShopNature, SeriesID, ModelID, MaterialID);
                var dt = CommHelper.DataTableHelper.LinqToDataTable(query.resultDetailList);
                if (dt.Columns.Count == 14)
                {
                    dt.Columns[0].ColumnName = "省份";
                    dt.Columns[6].ColumnName = "工厂编号";
                    dt.Columns[1].ColumnName = "仓库";
                    dt.Columns[2].ColumnName = "仓库类型";
                    dt.Columns[3].ColumnName = "店铺性质";
                    dt.Columns[4].ColumnName = "供应商";
                    dt.Columns[5].ColumnName = "货号";
                    dt.Columns[7].ColumnName = "成本价";
                    dt.Columns[8].ColumnName = "颜色";
                    dt.Columns[9].ColumnName = "售罄率";
                    dt.Columns[10].ColumnName = "尺码";
                    dt.Columns[11].ColumnName = "销售金额";
                    dt.Columns[12].ColumnName = "销售折扣";
                    dt.Columns[13].ColumnName = "销售数量";
                    dt.Columns[14].ColumnName = "当前库存";
                }
                var ds = new DataSet();
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

    }
}
